/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package accessor;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import model.Model;

/**
 *
 * @author Kirimi
 */
public class DepartmentAccessor extends Model{
    public List<Department> getAll(){
        List<Department> retVal = new ArrayList<Department>();
        Department department = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "Select * from tblDepartment where DepartmentID <> 1";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                department = new Department();
                department.setDepartmentID(rs.getInt("departmentID"));
                department.setDepartmentName(rs.getString("DepartmentName"));
                department.setDepartmentDescription(rs.getString("description"));
                
                retVal.add(department);
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return retVal;
    }
    
    
    public int updateDepartment(Department department){
        int retVal = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "Update tblDepartment set departmentName = ?, description = ? Where departmentID = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, department.getDepartmentName());
            ps.setString(2, department.getDepartmentDescription());
            ps.setInt(3, department.getDepartmentID());
            retVal = ps.executeUpdate();
            
            closeObject(ps);
            closeObject(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return retVal;
    }
    
    public Department getDepartmentByID(int departmentID){
        Department department = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "Select * from tblDepartment "
                    + " where tblDepartment.departmentID = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, departmentID);
            rs = ps.executeQuery();
            if(rs.next()){
                department = new Department();
                department.setDepartmentID(rs.getInt("departmentID"));
                department.setDepartmentName(rs.getString("departmentName"));
                department.setDepartmentDescription(rs.getString("description"));
            }
            closeObject(rs);
            closeObject(ps);
            closeObject(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return department;
    }
    
}
